<?php
declare (strict_types = 1);

namespace app\webadmin\controller;

use app\webadmin\BaseController;
use app\webadmin\model\Junyunxing;
use app\webadmin\validate\ChongfuCheck;
use think\facade\Db;
use think\facade\View;
use \think\facade\Log;
use app\webadmin\validate\WendingxingCheck;
use app\webadmin\model\Wendingxing;
use app\webadmin\model\Chongfu;
use think\exception\ValidateException;
use PhpOffice\PhpSpreadsheet\IOFactory;
use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;
use PhpOffice\PhpSpreadsheet\Style\Alignment;
use PhpOffice\PhpSpreadsheet\Style\Fill;
use PhpOffice\PhpSpreadsheet\Style\Border;
use PhpOffice\PhpSpreadsheet\Calculation\Statistical;
class Fenxi extends BaseController
{
    public function wendingxing()
    {
        if (request()->isAjax()) {
            $param = get_params();
            $where = array();
            if (!empty($param['keywords'])) {
                $where[] = ['id|times|datas', 'like', '%' . $param['keywords'] . '%'];
            }
            $where[]=['uid','=',get_login_admin('id')];
            $rows = empty($param['limit']) ? get_config('app.page_size') : $param['limit'];
            $content = Wendingxing::where($where)
                ->order('id asc')
                ->paginate($rows, false, ['query' => $param])->each(function ($item, $k) {
                    //$item['createtime']=date('Y-m-d H:i:s',$item['create_time']);

                    return $item;
                });
            return table_assign(0, '', $content);
        }
        else{
            return view();
        }
    }


    public function add_wendingxing(){
        $param = get_params();
        if (request()->isAjax()) {
            if (!empty($param['id']) && $param['id'] > 0) {
                //修改
                try {
                    validate(WendingxingCheck::class)->scene('edit')->check($param);
                } catch (ValidateException $e) {
                    // 验证失败 输出错误信息
                    return to_assign(1, $e->getError());
                }
                $param['update_time'] = time();
                $res = Wendingxing::strict(false)->field(true)->update($param);
                if ($res) {
                    add_log('edit', $param['id'], $param);
                    return to_assign(0, "修改成功");
                }
                return to_assign();
            } else {
                //新增
                try {
                    validate(WendingxingCheck::class)->scene('add')->check($param);
                } catch (ValidateException $e) {
                    // 验证失败 输出错误信息
                    return to_assign(1, $e->getError());
                }
                $param['create_time'] = time();
                $param['uid']=get_login_admin('id');

                $insertId = Wendingxing::strict(false)->field(true)->insertGetId($param);
                if ($insertId) {
                    add_log('add', $insertId, $param);
                    return to_assign(0, "添加成功");
                }
                return to_assign();
            }
        }
        else{
            $id = isset($param['id']) ? $param['id'] : 0;
            if ($id > 0) {
                $wdx = Wendingxing::where(['id' => $id])->find();
                if($wdx){
                }
                View::assign('data', $wdx);
            }
            View::assign('id', $id);
            return view();
        }
    }


    public function delete_wendingxing(){
        $db=Wendingxing::where('uid',get_login_admin('id'))->delete();
        return to_assign(0, "删除成功");
    }

    public function start_jisuan(){
        $where=[];
        $where[]=['uid','=',get_login_admin('id')];
        $wendingxings=Wendingxing::where($where)->order('id asc')->select();
        $i=0;
        $j=0;
        $sum=0;
        $sun_fenzi=0;
        $x_array=[];
        $y_array=[];
        foreach($wendingxings as $key=>$v){
            $i++;
            $datas=$v['datas'];
            $sum+=$datas;
            $x_array[]=$v['times'];
            $y_array[]=$datas;

        }
        //Log::info('x_array='.json_encode($x_array));
        //Log::info('y_array='.json_encode($y_array));
        $xielv=slope($x_array,$y_array);
        $avg=sprint_data($sum/$i,4);
        //截距
        $intercept = excel_intercept($x_array, $y_array);
        foreach($wendingxings as $key=>$v){
            $j++;
            $datas=$v['datas'];
            //$sum+=$datas;
            //$x_array[]=$v['times'];
            //$y_array[]=$datas;
            //(B21-D28-C28*A21)^2
            $fenzi=$datas-$intercept-$xielv*$v['times'];
            $fenzis=pow($fenzi,2);
            $wendingxings[$key]['fenzi']=$fenzis;
            //=SUM(E21:E27)
            $sun_fenzi+=$fenzis;
        }
        //=E28/(COUNT(A21:A27)-2)
        $spingfang=$sun_fenzi/($j-2);
        //=F28^0.5
        $s=$spingfang**0.5;
        //=(VARP(A21:A27)*COUNT(A21:A27))^0.5
        $sb1fm=pow(floatval(variance($x_array)['variance'])*count($x_array),0.5);
        //=G28/H28
        $sb1=$s/$sb1fm;
        //=TINV(0.05,(COUNT(A21:A27)-2)) 计算逆累积分布函数
        $fb1=Statistical::TINV(0.05,count($x_array)-2);
        $fbresult= $fb1*$sb1;
        Log::info('wendingxings='.json_encode($wendingxings));
        if($xielv<$fbresult){
            $zt='稳定';
        }else{
            $zt='不稳定';
        }
        $result=[
            ['key'=>'平均值','data'=>$avg],
            ['key'=>'斜率','data'=>sprint_data($xielv,4)],
            ['key'=>'截距','data'=>$intercept],
            //['key'=>'sun_fenzi','data'=>$sun_fenzi],
            //['key'=>'s平方','data'=>$spingfang],
            //['key'=>'s','data'=>$s],
            //['key'=>'sb1fm','data'=>$sb1fm],
           // ['key'=>'sb1','data'=>$sb1],
            ['key'=>'与斜率相关的不确定度','data'=>sprint_data($sb1,3)],
            ['key'=>'t<sub>0.95,n-2</sub>','data'=>sprint_data($fb1,3)],
            ['key'=>'t<sub>0.95,n-2*S(b1)</sub>','data'=>sprint_data($fbresult,3)],
            ['key'=>'稳定性判断','data'=>$zt],
        ];
        $html = '';
        if ($result) {
            foreach ($result as $h) {
                $html .= "<tr><td align=\"center\">{$h['key']}</td><td align=\"center\">{$h['data']}</td></tr>";
            }
        }
        echo $html;
    }

    public function export_data(){
        $where=[];
        $where[]=['uid','=',get_login_admin('id')];
        $wendingxings=Wendingxing::where($where)->order('id asc')->select();
        $i=0;
        $j=0;
        $sum=0;
        $sun_fenzi=0;
        $x_array=[];
        $y_array=[];
        foreach($wendingxings as $key=>$v){
            $i++;
            $datas=$v['datas'];
            $sum+=$datas;
            $x_array[]=$v['times'];
            $y_array[]=$datas;

        }
        //Log::info('x_array='.json_encode($x_array));
        //Log::info('y_array='.json_encode($y_array));
        $xielv=slope($x_array,$y_array);
        $avg=sprint_data($sum/$i,4);
        //截距
        $intercept = excel_intercept($x_array, $y_array);
        foreach($wendingxings as $key=>$v){
            $j++;
            $datas=$v['datas'];

            //(B21-D28-C28*A21)^2
            $fenzi=$datas-$intercept-$xielv*$v['times'];
            $fenzis=pow($fenzi,2);
            $wendingxings[$key]['fenzi']=$fenzis;
            //=SUM(E21:E27)
            $sun_fenzi+=$fenzis;
        }
        //=E28/(COUNT(A21:A27)-2)
        $spingfang=$sun_fenzi/($j-2);
        //=F28^0.5
        $s=$spingfang**0.5;
        //=(VARP(A21:A27)*COUNT(A21:A27))^0.5
        $sb1fm=pow(floatval(variance($x_array)['variance'])*count($x_array),0.5);
        //=G28/H28
        $sb1=$s/$sb1fm;
        //=TINV(0.05,(COUNT(A21:A27)-2)) 计算逆累积分布函数
        $fb1=Statistical::TINV(0.05,count($x_array)-2);
        $fbresult= $fb1*$sb1;
        Log::info('wendingxings='.json_encode($wendingxings));
        if($xielv<$fbresult){
            $zt='稳定';
        }else{
            $zt='不稳定';
        }
        $result=[
            ['key'=>'平均值','data'=>$avg],
            ['key'=>'斜率','data'=>sprint_data($xielv,3)],
            // ['key'=>'截距','data'=>$intercept],
            //['key'=>'sun_fenzi','data'=>$sun_fenzi],
            //['key'=>'s平方','data'=>$spingfang],
            //['key'=>'s','data'=>$s],
            // ['key'=>'sb1fm','data'=>$sb1fm],
            // ['key'=>'sb1','data'=>$sb1],
            ['key'=>'与斜率相关的不确定度','data'=>sprint_data($sb1,3)],
            ['key'=>'t0.95,n-2','data'=>sprint_data($fb1,3)],
            ['key'=>'t0.95,n-2*S(b1)','data'=>sprint_data($fbresult,3)],
            ['key'=>'稳定性判断','data'=>$zt],
        ];
        //导出表格
        $objExcel = new Spreadsheet();
        $objWriter = IOFactory::createWriter($objExcel, 'Xlsx');
        $objActSheet = $objExcel->getActiveSheet(0);
        $objActSheet->setTitle('稳定性分析');//设置excel的标题

        $objActSheet->getRowDimension(1)->setRowHeight(30);
        $objActSheet->getStyle('A1:B1')->getFont()->setSize(14);
        $objActSheet->getStyle('A1:B1')->getFill()->setFillType(Fill::FILL_SOLID)->getStartColor()->setRGB('CCCCCC');
        // 设置列宽
        $objActSheet->getColumnDimension('A')->setWidth(40);
        $objActSheet->getColumnDimension('B')->setWidth(20);

        $objActSheet->getRowDimension(1)->setRowHeight(30);      // 设置行高

        //设置水平、垂直居中
        $styleArray = [
            'alignment' => [
                'horizontal' => \PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_CENTER,
                'vertical' => \PhpOffice\PhpSpreadsheet\Style\Alignment::VERTICAL_CENTER,
            ],
        ];

        $objActSheet->getStyle('A1:B100')->applyFromArray($styleArray);
        $objActSheet->setCellValue('A1', '结果名称');
        $objActSheet->setCellValue('B1', '结果');
        $baseRow = 2; //数据从N-1行开始往下输出
        foreach ($result as $r => $d) {

            $i = $baseRow + $r;
            $objActSheet->getRowDimension($i)->setRowHeight(30);
            $objActSheet->getStyle('A' . $i . ':B' . $i)->getFont()->setSize(12);
            $objActSheet->setCellValue('A' . $i, $d['key']);
            $objActSheet->setCellValue('B' . $i, $d['data']);
         }

        header('Content-Type: application/vnd.ms-excel');
        header('Content-Disposition: attachment;filename="稳定性分析（'. date('Y-m-d')  .'）'.'.xlsx"');
        header('Cache-Control: max-age=0');
        $objWriter->save('php://output');
    }
    public function export_data2(){
        $where=[];
        $where[]=['uid','=',get_login_admin('id')];
        $wendingxings=Wendingxing::where($where)->order('id asc')->select();
        $i=0;
        $j=0;
        $sum=0;
        $sun_fenzi=0;
        $x_array=[];
        $y_array=[];
        foreach($wendingxings as $key=>$v){
            $i++;
            $datas=$v['datas'];
            $sum+=$datas;
            $x_array[]=$v['times'];
            $y_array[]=$datas;

        }
        //Log::info('x_array='.json_encode($x_array));
        //Log::info('y_array='.json_encode($y_array));
        $xielv=slope($x_array,$y_array);
        $avg=sprint_data($sum/$i,4);
        //截距
        $intercept = excel_intercept($x_array, $y_array);
        foreach($wendingxings as $key=>$v){
            $j++;
            $datas=$v['datas'];
            //$sum+=$datas;
            //$x_array[]=$v['times'];
            //$y_array[]=$datas;
            //(B21-D28-C28*A21)^2
            $fenzi=$datas-$intercept-$xielv*$v['times'];
            $fenzis=pow($fenzi,2);
            $wendingxings[$key]['fenzi']=$fenzis;
            //=SUM(E21:E27)
            $sun_fenzi+=$fenzis;
        }
        //=E28/(COUNT(A21:A27)-2)
        $spingfang=$sun_fenzi/($j-2);
        //=F28^0.5
        $s=$spingfang**0.5;
        //=(VARP(A21:A27)*COUNT(A21:A27))^0.5
        $sb1fm=pow(floatval(variance($x_array)['variance'])*count($x_array),0.5);
        //=G28/H28
        $sb1=$s/$sb1fm;
        //=TINV(0.05,(COUNT(A21:A27)-2)) 计算逆累积分布函数
        $fb1=Statistical::TINV(0.05,5);
        $fbresult= $fb1*$sb1;
        //Log::info('wendingxings='.json_encode($wendingxings));
        if($xielv<$fbresult){
            $zt='稳定';
        }else{
            $zt='不稳定';
        }
        $result=[
            ['key'=>'平均值','data'=>$avg],
            ['key'=>'斜率','data'=>$xielv],
            // ['key'=>'截距','data'=>$intercept],
            //['key'=>'sun_fenzi','data'=>$sun_fenzi],
            //['key'=>'s平方','data'=>$spingfang],
            //['key'=>'s','data'=>$s],
            // ['key'=>'sb1fm','data'=>$sb1fm],
            // ['key'=>'sb1','data'=>$sb1],
            ['key'=>'与斜率相关的不确定度','data'=>sprint_data($sb1,4)],
            ['key'=>'t0.95,n-2','data'=>sprint_data($fb1,4)],
            ['key'=>'t0.95,n-2*S(b1)','data'=>sprint_data($fbresult,4)],
            ['key'=>'稳定性判断','data'=>$zt],
        ];
        Log::info('result='.json_encode($result));
        $filename='wdx_'.date('YmdHis');
        $resultdata=exportexcel($result,array('结果名称','结果'),$filename);
    }

    //可重复性
    public function chongfu()
    {
        if (request()->isAjax()) {
            $param = get_params();
            $where = array();
            if (!empty($param['keywords'])) {
                $where[] = ['id|times|datas', 'like', '%' . $param['keywords'] . '%'];
            }
            $where[]=['uid','=',get_login_admin('id')];
            $rows = empty($param['limit']) ? get_config('app.page_size') : $param['limit'];
            $content = Chongfu::where($where)
                ->order('id asc')
                ->paginate($rows, false, ['query' => $param])->each(function ($item, $k) {
                    //$item['createtime']=date('Y-m-d H:i:s',$item['create_time']);

                    return $item;
                });
            return table_assign(0, '', $content);
        }
        else{
            return view();
        }
    }

    public function delete_chongfu(){
        $db=Chongfu::where('uid',get_login_admin('id'))->delete();

        return to_assign(0, "删除成功");
    }

    public function add_chongfu(){
        $param = get_params();
        if (request()->isAjax()) {
            if (!empty($param['id']) && $param['id'] > 0) {
                //修改
                try {
                    validate(ChongfuCheck::class)->scene('edit')->check($param);
                } catch (ValidateException $e) {
                    // 验证失败 输出错误信息
                    return to_assign(1, $e->getError());
                }
                $param['update_time'] = time();
                $res = Chongfu::strict(false)->field(true)->update($param);
                if ($res) {
                    add_log('edit', $param['id'], $param);
                    return to_assign(0, "修改成功");
                }
                return to_assign();
            } else {
                //新增
                try {
                    validate(ChongfuCheck::class)->scene('add')->check($param);
                } catch (ValidateException $e) {
                    // 验证失败 输出错误信息
                    return to_assign(1, $e->getError());
                }
                $param['create_time'] = time();
                $param['uid']=get_login_admin('id');
                $insertId = Chongfu::strict(false)->field(true)->insertGetId($param);
                if ($insertId) {
                    add_log('add', $insertId, $param);
                    return to_assign(0, "添加成功");
                }
                return to_assign();
            }
        }
        else{
            $id = isset($param['id']) ? $param['id'] : 0;
            if ($id > 0) {
                $cf = Chongfu::where(['id' => $id])->find();
                if($cf){
                }
                View::assign('data', $cf);
            }
            View::assign('id', $id);
            return view();
        }
    }

    //计算重复性
    public function start_chongfu(){

        $where=[];
        $where[]=['uid','=',get_login_admin('id')];
        $chongfus=Chongfu::where($where)->order('id asc')->select();
        $i=0;
        foreach($chongfus as $key=>$v){
            $i++;
            $avg=$v['avgs'];
            $sds=$v['sds'];
            $first_d=getChongfuAvg();
            $first_sds=getChongfuSds();
            $d=$first_d-$avg;
            $dt= pow((((pow($sds,2))*(6-1)+(pow($first_sds,2))*(6-1))*6*2/((6*2-2)*(pow(6,2)))),0.5);

            if($key==0){
                $chongfus[$key]['dt']='';
                $chongfus[$key]['dt']='';
            }else{
                $chongfus[$key]['d']=sprint_data($d,2);
                $chongfus[$key]['dt']=sprint_data($dt,2);
                $chongfus[$key]['t']=sprint_data($d/$dt,2);
            }
            $g= Statistical::TINV(0.05,(6*2-2));
            $chongfus[$key]['g']=sprint_data($g,2);
            if($d/$dt<$g){
                $result='一致';
            }else{
                $result='<font color=red>不一致</font>';
            }
            $chongfus[$key]['result']=$result;
        }
        $html = '';
        if ($chongfus) {
            foreach ($chongfus as $h) {
                $html .= "<tr><td align=\"center\">{$h['cishu']}</td><td align=\"center\">{$h['avgs']}</td><td align='center'>{$h['d']}</td><td>{$h['dt']}</td><td>{$h['t']}</td><td>{$h['g']}</td><td>{$h['result']}</td></tr>";
            }
        }
        echo $html;
    }

    //数据导出
    public function export_chongfu_data(){

        $where=[];
        $where[]=['uid','=',get_login_admin('id')];
        $chongfus=Chongfu::where($where)->order('id asc')->select();
        $i=0;
        foreach($chongfus as $key=>$v){
            $i++;
            $avg=$v['avgs'];
            $sds=$v['sds'];
            $first_d=getChongfuAvg();
            $first_sds=getChongfuSds();
            $d=$first_d-$avg;
            $dt= pow((((pow($sds,2))*(6-1)+(pow($first_sds,2))*(6-1))*6*2/((6*2-2)*(pow(6,2)))),0.5);

            if($key==0){
                $chongfus[$key]['dt']='';
                $chongfus[$key]['dt']='';
            }else{
                $chongfus[$key]['d']=sprint_data($d,2);
                $chongfus[$key]['dt']=sprint_data($dt,2);
                $chongfus[$key]['t']=sprint_data($d/$dt,2);
            }
            $g= Statistical::TINV(0.05,(6*2-2));
            $chongfus[$key]['g']=sprint_data($g,2);
            if($d/$dt<$g){
                $result='一致';
            }else{
                $result='不一致';
            }
            $chongfus[$key]['result']=$result;
        }
        //导出表格
        $objExcel = new Spreadsheet();
        $objWriter = IOFactory::createWriter($objExcel, 'Xlsx');
        $objActSheet = $objExcel->getActiveSheet(0);
        $objActSheet->setTitle('重复数可扩展');//设置excel的标题

        $objActSheet->getRowDimension(1)->setRowHeight(25);
        $objActSheet->getStyle('A1:G1')->getFont()->setSize(14);
        $objActSheet->getStyle('A1:G1')->getFill()->setFillType(Fill::FILL_SOLID)->getStartColor()->setRGB('CCCCCC');
        // 设置列宽
        $objActSheet->getColumnDimension('A')->setWidth(10);
        $objActSheet->getColumnDimension('B')->setWidth(20);
        $objActSheet->getColumnDimension('C')->setWidth(20);
        $objActSheet->getColumnDimension('D')->setWidth(20);
        $objActSheet->getColumnDimension('E')->setWidth(20);
        $objActSheet->getColumnDimension('F')->setWidth(20);
        $objActSheet->getColumnDimension('G')->setWidth(20);
        $objActSheet->getRowDimension(1)->setRowHeight(25);      // 设置行高

        //设置水平、垂直居中
        $styleArray = [
            'alignment' => [
                'horizontal' => \PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_CENTER,
                'vertical' => \PhpOffice\PhpSpreadsheet\Style\Alignment::VERTICAL_CENTER,
            ],
        ];

        $objActSheet->getStyle('A1:G100')->applyFromArray($styleArray);
        $objActSheet->setCellValue('A1', '冻融次数');
        $objActSheet->setCellValue('B1', '平均值');
        $objActSheet->setCellValue('C1', 'D');
        $objActSheet->setCellValue('D1', 'E');
        $objActSheet->setCellValue('E1', 't值');
        $objActSheet->setCellValue('F1', 't(0.05,4)');
        $objActSheet->setCellValue('G1', '结果');
        $baseRow = 2; //数据从N-1行开始往下输出
        foreach ($chongfus as $r => $d) {

            $i = $baseRow + $r;
            $objActSheet->getRowDimension($i)->setRowHeight(25);
            $objActSheet->getStyle('A' . $i . ':G' . $i)->getFont()->setSize(12);
            $objActSheet->setCellValue('A' . $i, $d['cishu']);
            $objActSheet->setCellValue('B' . $i, $d['avgs']);
            $objActSheet->setCellValue('C' . $i, $d['d']);
            $objActSheet->setCellValue('D' . $i, $d['dt']);
            $objActSheet->setCellValue('E' . $i, $d['t']);
            $objActSheet->setCellValue('F' . $i, $d['g']);
            $objActSheet->setCellValue('G' . $i, $d['result']);

        }

        header('Content-Type: application/vnd.ms-excel');
        header('Content-Disposition: attachment;filename="重复可扩展（'. date('Y-m-d')  .'）'.'.xlsx"');
        header('Cache-Control: max-age=0');
        $objWriter->save('php://output');
    }

    //均匀性分析
    public function junyunxing(){
        if (request()->isAjax()) {
            $param = get_params();
            $where = array();
            if (!empty($param['keywords'])) {
                $where[] = ['id|nums|yp1', 'like', '%' . $param['keywords'] . '%'];
            }
            $where[]=['uid','=',get_login_admin('id')];
            $rows = empty($param['limit']) ? get_config('app.page_size') : $param['limit'];
            $data1=0;
            $content = Junyunxing::where($where)
                ->order('id asc')
                ->paginate($rows, false, ['query' => $param])->each(function ($item, $k)use ($data1) {
                    //$item['createtime']=date('Y-m-d H:i:s',$item['create_time']);

                    return $item;
                });
            Log::info('content='.json_encode($content));
            return table_assign(0, '', $content);
        }
        else{
            return view();
        }
    }

    //删除均匀性
    public function delete_junyunxing(){
        $db=Junyunxing::where('uid',get_login_admin('id'))->delete();

        return to_assign(0, "删除成功");
    }

    //开始计算均匀性
    public function start_junyunxing(){

        $where=[];
        $where[]=['uid','=',get_login_admin('id')];
        $junyunxing=Junyunxing::where($where)->order('id asc')->select();
        $i=0;
        $j=0;
        $sum_data1=0;
        $sum_data2=0;
        $sum_data3=0;
        foreach($junyunxing as $key=>$v){
            $i++;
            $data1=$v['data1'];
            $data2=$v['data2'];
            $data3=$v['data3'];
            $sum_data1+=$data1;
            $sum_data2+=$data2;
            $sum_data3+=$data3;
        }

        $new_arr=Junyunxing::order('id asc')->column('yp1,yp2,yp3');
        //echo json_encode(flatten($new_arr));
        $range=flatten($new_arr);
        $average = Statistical::AVERAGE($range);
        $result=[
            'nums'=>count($junyunxing),'avgs'=>$average
        ];
        Log::info('range11='.json_encode($range));
        $stdDev = Statistical::STDEV($range);

        //=D26/D25*100
        $rsds=sprint_data(($stdDev/$average)*100,9);
        $result2=[
            'chongfushu'=>3,
            'sd'=>$stdDev,
            'rsds'=>$rsds
        ];
        //=G24/B26-E24^2/B25/B26
        $zja=ceil($sum_data3/3-pow($sum_data1,2)/count($junyunxing)/3);
        $zja2=count($junyunxing)-1;
        //=B28/C28
        $zja3=ceil($zja/$zja2);

        //=F24-G24/B26
        $zne=ceil($sum_data2-($sum_data3/3));
        //=B25*(B26-1)
        $zne2=count($junyunxing)*(3-1);
        //=B29/C29
        $zne3=intval($zne/$zne2);

        //=D28/D29
        $zja4=sprint_data($zja3/$zne3,2);

        //=FINV(0.05,C28,C29)
       // $zja5=finv2(0.05,30,14);//有问题
        $zja5=0;
        //=((D28-D29)/B26)^0.5
        $sbb=sprint_data(pow(($zja3-$zne3)/3,0.5),7);
        //=(D29/B26)^0.5*(2/C29)^0.25
        $ubb=pow($zne3/3,0.5)*pow((2/30),0.25);
        if($sbb>$ubb){
            $bqdx=$sbb;
        }else{
            $bqdx=$ubb;
        }

        //=B34/D25
        $xdbqdx=sprint_data($bqdx/$result['avgs'],9);
        //Log::info('result='.json_encode($result));
        $html = '<table class="layui-table layui-table-form">';
        if ($result) {
            $html .= "<tr><td align=\"center\">数据个数</td><td>{$result['nums']}</td><td>平均值</td><td align=\"center\">{$result['avgs']}</td></tr>";
            $html .= "<tr><td align=\"center\">重复数</td><td>{$result2['chongfushu']}</td><td>SD值</td><td align=\"center\">{$result2['sd']}</td><td>RSD</td><td>{$result2['rsds']}</td></tr>";
            $html .= "<tr><td align=\"center\">差来源</td><td>差方和(Q)</td><td>自由度(f)</td><td align=\"center\">均方(MS)</td><td>统计量(F)</td><td>F(0.05,v1,v2)</td></tr>";
            $html .= "<tr><td align=\"center\">组间A</td><td>{$zja}</td><td>{$zja2}</td><td align=\"center\">{$zja3}</td><td>{$zja4}</td><td>{$zja5}</td></tr>";
            $html .= "<tr><td align=\"center\">组内e</td><td>{$zne}</td><td>{$zne2}</td><td align=\"center\">{$zne3}</td><td></td><td></td></tr>";
            $html .= "<tr><td align=\"center\">sbb</td><td>{$sbb}</td></tr>";
            $html .= "<tr><td align=\"center\">ubb</td><td>{$ubb}</td></tr>";
            $html .= "<tr><td align=\"center\">均匀性不确定度</td><td>{$bqdx}</td></tr>";
            $html .= "<tr><td align=\"center\">均匀性相对不确定度</td><td>{$xdbqdx}</td></tr>";

        }
        $html.='</table>';
        Log::info('html='.$html);
        echo $html;
    }

    public function export_junyunxing_data(){

        $where=[];
        $where[]=['uid','=',get_login_admin('id')];
        $junyunxing=Junyunxing::where($where)->order('id asc')->select();
        $i=0;
        $sum_data1=0;
        $sum_data2=0;
        $sum_data3=0;
        foreach($junyunxing as $key=>$v){
            $i++;
            $data1=$v['data1'];
            $data2=$v['data2'];
            $data3=$v['data3'];
            $sum_data1+=$data1;
            $sum_data2+=$data2;
            $sum_data3+=$data3;
        }

        $new_arr=Junyunxing::order('id asc')->column('yp1,yp2,yp3');
        //echo json_encode(flatten($new_arr));
        $range=flatten($new_arr);
        Log::info('range11='.json_encode($range));
        $average = Statistical::AVERAGE($range);
        $result=[
            'nums'=>count($junyunxing),'avgs'=>$average
        ];
        $stdDev = Statistical::STDEV($range);

        //=D26/D25*100
        $rsds=sprint_data(($stdDev/$average)*100,9);
        $result2=[
            'chongfushu'=>3,
            'sd'=>$stdDev,
            'rsds'=>$rsds
        ];
        //=G24/B26-E24^2/B25/B26
        $zja=ceil($sum_data3/3-pow($sum_data1,2)/count($junyunxing)/3);
        $zja2=count($junyunxing)-1;
        //=B28/C28
        $zja3=ceil($zja/$zja2);

        //=F24-G24/B26
        $zne=ceil($sum_data2-($sum_data3/3));
        //=B25*(B26-1)
        $zne2=count($junyunxing)*(3-1);
        //=B29/C29
        $zne3=intval($zne/$zne2);

        //=D28/D29
        $zja4=sprint_data($zja3/$zne3,2);

        //=FINV(0.05,C28,C29)
        // $zja5=finv2(0.05,30,14);//有问题
        $zja5=0;
        //=((D28-D29)/B26)^0.5
        $sbb=sprint_data(pow(($zja3-$zne3)/3,0.5),7);
        //=(D29/B26)^0.5*(2/C29)^0.25
        $ubb=pow($zne3/3,0.5)*pow((2/30),0.25);
        if($sbb>$ubb){
            $bqdx=$sbb;
        }else{
            $bqdx=$ubb;
        }

        //=B34/D25
        $xdbqdx=sprint_data($bqdx/$result['avgs'],9);
        //Log::info('result='.json_encode($result));
        //导出表格
        $objExcel = new Spreadsheet();
        $objWriter = IOFactory::createWriter($objExcel, 'Xlsx');
        $objActSheet = $objExcel->getActiveSheet(0);
        $objActSheet->setTitle('均匀性分析');//设置excel的标题

        $objActSheet->getStyle('A1:F1')->getFont()->setSize(14);
        $objActSheet->getStyle('A1:F1')->getFill()->setFillType(Fill::FILL_SOLID)->getStartColor()->setRGB('CCCCCC');
        // 设置列宽
        $objActSheet->getColumnDimension('A')->setWidth(20);
        $objActSheet->getColumnDimension('B')->setWidth(20);
        $objActSheet->getColumnDimension('C')->setWidth(20);
        $objActSheet->getColumnDimension('D')->setWidth(20);
        $objActSheet->getColumnDimension('E')->setWidth(20);
        $objActSheet->getColumnDimension('F')->setWidth(20);
        $objActSheet->getRowDimension(1)->setRowHeight(25);      // 设置行高

        //设置水平、垂直居中
        $styleArray = [
            'alignment' => [
                'horizontal' => \PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_CENTER,
                'vertical' => \PhpOffice\PhpSpreadsheet\Style\Alignment::VERTICAL_CENTER,
            ],
        ];

        $objActSheet->getStyle('A1:F100')->applyFromArray($styleArray);
        $objActSheet->setCellValue('A1', '数据个数');
        $objActSheet->setCellValue('B1', $result['nums']);
        $objActSheet->setCellValue('C1', '平均值');
        $objActSheet->setCellValue('D1',  $result['avgs']);
        $objActSheet->setCellValue('E1', '');
        $objActSheet->setCellValue('F1', '');
        $objActSheet->getRowDimension(2)->setRowHeight(25);

        $objActSheet->setCellValue('A2', '重复数');
        $objActSheet->setCellValue('B2', $result2['chongfushu']);
        $objActSheet->setCellValue('C2', 'SD值');
        $objActSheet->setCellValue('D2',  $result2['sd']);
        $objActSheet->setCellValue('E2', '');
        $objActSheet->setCellValue('F2', '');
        $objActSheet->getRowDimension(3)->setRowHeight(25);

        $objActSheet->setCellValue('A3', '差来源');
        $objActSheet->setCellValue('B3', '差方和(Q)');
        $objActSheet->setCellValue('C3', '自由度(f)');
        $objActSheet->setCellValue('D3',  '均方(MS)');
        $objActSheet->setCellValue('E3', '统计量(F)');
        $objActSheet->setCellValue('F3', 'F(0.05,v1,v2)');
        $objActSheet->getRowDimension(4)->setRowHeight(25);


        $objActSheet->setCellValue('A4', '组间A');
        $objActSheet->setCellValue('B4', $zja);
        $objActSheet->setCellValue('C4', $zja2);
        $objActSheet->setCellValue('D4', $zja3);
        $objActSheet->setCellValue('E4', $zja4);
        $objActSheet->setCellValue('F4', '=FINV(0.05,C4,D4)');
        $objActSheet->getRowDimension(5)->setRowHeight(25);


        $objActSheet->setCellValue('A5', '组内e');
        $objActSheet->setCellValue('B5', $zne);
        $objActSheet->setCellValue('C5', $zne2);
        $objActSheet->setCellValue('D5', $zne3);
        $objActSheet->setCellValue('E5', '');
        $objActSheet->setCellValue('F5', '');
        $objActSheet->getRowDimension(6)->setRowHeight(25);

        $objActSheet->setCellValue('A6', 'sbb');
        $objActSheet->setCellValue('B6', $sbb);
        $objActSheet->setCellValue('C6', '');
        $objActSheet->setCellValue('D6', '');
        $objActSheet->setCellValue('E6', '');
        $objActSheet->setCellValue('F6', '');

        $objActSheet->setCellValue('A7', 'ubb');
        $objActSheet->setCellValue('B7', $ubb);
        $objActSheet->setCellValue('C7', '');
        $objActSheet->setCellValue('D7', '');
        $objActSheet->setCellValue('E7', '');
        $objActSheet->setCellValue('F7', '');
        $objActSheet->getRowDimension(7)->setRowHeight(25);

        $objActSheet->setCellValue('A8', '均匀性不确定度');
        $objActSheet->setCellValue('B8', $bqdx);
        $objActSheet->setCellValue('C8', '');
        $objActSheet->setCellValue('D8', '');
        $objActSheet->setCellValue('E8', '');
        $objActSheet->setCellValue('F8', '');
        $objActSheet->getRowDimension(8)->setRowHeight(25);

        $objActSheet->setCellValue('A9', '均匀性相对不确定度');
        $objActSheet->setCellValue('B9', $xdbqdx);
        $objActSheet->setCellValue('C9', '');
        $objActSheet->setCellValue('D9', '');
        $objActSheet->setCellValue('E9', '');
        $objActSheet->setCellValue('F9', '');
        $objActSheet->getRowDimension(9)->setRowHeight(25);

        header('Content-Type: application/vnd.ms-excel');
        header('Content-Disposition: attachment;filename="均匀性分析（'. date('Y-m-d')  .'）'.'.xlsx"');
        header('Cache-Control: max-age=0');
        $objWriter->save('php://output');
    }


}